using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class Stat : IStat
    {
        // Methods
        public IList GetAcivationStatMonth()
        {
            string cmdText = "select DISTINCT STAT_MONTH from STAT_ACTIVATION_MONTH where ISDEL ='0' order by STAT_MONTH desc";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }
            }
            return list;
        }

        public IList GetBBSMonthStatMonth()
        {
            string cmdText = "select DISTINCT STAT_MONTH from STAT_BBS_MONTH where ISDEL ='0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }
            }
            return list;
        }

        public IList GetLoginMonthStatMonth()
        {
            string cmdText = "select DISTINCT STAT_MONTH from STAT_LOGIN_MOTH where ISDEL ='0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }
            }
            return list;
        }

        public DataTable GetPvStat(string sBeginTime, string sEndTime)
        {
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("p_getpvstat", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@begin", sBeginTime);
                command.Parameters.AddWithValue("@end", sEndTime);
                command.Parameters.Add("@ipcount", SqlDbType.Int);
                command.Parameters.Add("@minutescount", SqlDbType.Int);
                command.Parameters.Add("@timescount", SqlDbType.Int);
                command.Parameters["@ipcount"].Direction = ParameterDirection.Output;
                command.Parameters["@minutescount"].Direction = ParameterDirection.Output;
                command.Parameters["@timescount"].Direction = ParameterDirection.Output;
                command.ExecuteNonQuery();
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("IpCount", typeof(int)));
                table.Columns.Add(new DataColumn("MinutesCount", typeof(int)));
                table.Columns.Add(new DataColumn("TimesCount", typeof(int)));
                DataRow row = table.NewRow();
                row["IpCount"] = command.Parameters["@ipcount"].Value;
                row["MinutesCount"] = command.Parameters["@minutescount"].Value;
                row["TimesCount"] = command.Parameters["@timescount"].Value;
                table.Rows.Add(row);
                return table;
            }
        }

        public IList GetScoreMonthStatMonth()
        {
            string cmdText = "select DISTINCT STAT_MONTH from STAT_SCORE_MONTH where ISDEL ='0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }
            }
            return list;
        }

        public DataTable SearchActivation(int Stat_Month)
        {
            int intValue = ConfigUtil.GetIntValue("ActionStat");
            string cmdText = string.Concat(new object[] { "select st.STAT_MONTH,isnull(it.Item_Name,'') as Item_Name,st.STAT_COUNTS from STAT_ACTIVATION_MONTH st inner join Item_Tbl it on st.STAT_ACTI_TYPE=it.Item_Value where st.ISDEL ='0' and it.Parent_Item_ID =", intValue, " and STAT_MONTH = ", Stat_Month });
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(cmdText, connection);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
            return dataTable;
        }

        public DataTable SearchBBSMonth(int Stat_Month)
        {
            int intValue = ConfigUtil.GetIntValue("BBSStat");
            string cmdText = string.Concat(new object[] { "select st.Stat_month,isnull(it.Item_Name,'') as Item_Name,isnull(STAT_COUNTS,'') as STAT_COUNTS from STAT_BBS_MONTH st inner join dbo.Item_Tbl it on st.STAT_BBS_TYPE = it.Item_Value where it.Parent_Item_ID = ", intValue, " and STAT_MONTH = ", Stat_Month });
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(cmdText, connection);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
            return dataTable;
        }

        public DataTable SearchLoginMonth(int Stat_Month)
        {
            string cmdText = string.Empty;
            int intValue = ConfigUtil.GetIntValue("LoginStat");
            cmdText = string.Concat(new object[] { "select st.Stat_month,isnull(it.Item_Name,'') as Item_Name,isnull(st.stat_counts,'') as stat_counts from dbo.STAT_LOGIN_MONTH st inner join dbo.Item_Tbl it on st.stat_login_type = it.Item_Value where it.Parent_Item_ID =", intValue, " and STAT_MONTH = ", Stat_Month });
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(cmdText, connection);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
            return dataTable;
        }

        public DataTable SearchScoreMonth(int Stat_Month)
        {
            int intValue = ConfigUtil.GetIntValue("ScoreStat");
            string cmdText = string.Concat(new object[] { "select st.Stat_month,isnull(it.Item_Name,'') as Item_Name,isnull(STAT_COUNTS,'') as STAT_COUNTS,isnull(STAT_SCORES,'') as STAT_SCORES from STAT_SCORE_MONTH st inner join dbo.Item_Tbl it on st.STAT_SCORE_TYPE = it.Item_Value where it.Parent_Item_ID = ", intValue, " and STAT_MONTH = ", Stat_Month });
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(cmdText, connection);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
            return dataTable;
        }
    }
}